﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DataAccess
{
    public class AdmissionDAO
    {
        ConnectionDatabase conn;
        SqlConnection con;
        SqlCommand cmd;
        SqlDataAdapter da;

        public AdmissionDAO()
        {
            conn = new ConnectionDatabase();
        }
        public void AddAdmission(int CodeProfileStudent, int CodeSchoolSubjects, int CodeSchoolofYear, int CodeSemester, string CodeFaculty, string FullName, string DateOfBirth, string Gender, string FatherName, string MotherName, string ResidentalAddress, string PernamentAddress)
        {
            con = conn.GetConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("SP_AddAdmission", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CodeProfileStudent", SqlDbType.Int).Value = CodeProfileStudent;
                cmd.Parameters.Add("@CodeSchoolSubjects", SqlDbType.Int).Value = CodeSchoolSubjects;
                cmd.Parameters.Add("@CodeSchoolofYear", SqlDbType.Int).Value = CodeSchoolofYear;
                cmd.Parameters.Add("@CodeSemester", SqlDbType.Int).Value = CodeSemester;
                cmd.Parameters.Add("@CodeFaculty", SqlDbType.VarChar).Value = CodeFaculty;
                cmd.Parameters.Add("@FullName", SqlDbType.VarChar).Value = FullName;
                cmd.Parameters.Add("@DateOfBirth", SqlDbType.SmallDateTime).Value = DateOfBirth;
                cmd.Parameters.Add("@Gender", SqlDbType.VarChar).Value = Gender;
                cmd.Parameters.Add("@FatherName", SqlDbType.VarChar).Value = FatherName;
                cmd.Parameters.Add("@MotherName", SqlDbType.VarChar).Value = MotherName;
                cmd.Parameters.Add("@ResidentalAddress", SqlDbType.VarChar).Value = ResidentalAddress;
                cmd.Parameters.Add("@PernamentAddress", SqlDbType.VarChar).Value = PernamentAddress;
                cmd.ExecuteNonQuery();

            }
            catch (Exception e) { throw; }
            finally { con.Close(); }
        }
        //xem thông tin của Admission
        public DataTable ViewAdmission()
        {
            DataTable tbl = conn.ExecuteQueryDataTable("SP_ViewAdmission");
            return tbl;
        }
        // xem thông tin của Admission theo từng môn 
        public DataTable ViewAdmissionSubject(int CodeSchoolSubjects)
        {
            DataTable tbl;
            con = conn.GetConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("SP_AddAdmission", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CodeSchoolSubjects", SqlDbType.Int).Value = CodeSchoolSubjects;
                da = new SqlDataAdapter(cmd);
                tbl = new DataTable();
                da.Fill(tbl);

            }
            catch (Exception e) { throw; }
            finally { con.Close(); }
            return tbl;
        }
        // admin check xem đơn đăng nhập 
        public void CheckAdmissionYes(int CodeSchoolSubjects, int CodeProfileStudent)
        {
            con = conn.GetConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("SP_checkYes", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CodeProfileStudent", SqlDbType.Int).Value = CodeProfileStudent;
                cmd.Parameters.Add("@CodeSchoolSubjects", SqlDbType.Int).Value = CodeSchoolSubjects;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e) { throw; }
            finally { con.Close(); }
        }
        public void CheckAdmissionNo(int CodeSchoolSubjects, int CodeProfileStudent)
        {
            con = conn.GetConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("SP_checkNo", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CodeProfileStudent", SqlDbType.Int).Value = CodeProfileStudent;
                cmd.Parameters.Add("@CodeSchoolSubjects", SqlDbType.Int).Value = CodeSchoolSubjects;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e) { throw; }
            finally { con.Close(); }
        }
        public void CheckAdmissionWait(int CodeSchoolSubjects, int CodeProfileStudent)
        {
            con = conn.GetConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("SP_checkWait", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CodeProfileStudent", SqlDbType.Int).Value = CodeProfileStudent;
                cmd.Parameters.Add("@CodeSchoolSubjects", SqlDbType.Int).Value = CodeSchoolSubjects;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e) { throw; }
            finally { con.Close(); }
        }
        // cho phép sinh viên đồng ý vào học
        public void Status(int CodeSchoolSubjects, int CodeProfileStudent)
        {
            con = conn.GetConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("SP_Status", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CodeProfileStudent", SqlDbType.Int).Value = CodeProfileStudent;
                cmd.Parameters.Add("@CodeSchoolSubjects", SqlDbType.Int).Value = CodeSchoolSubjects;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e) { throw; }
            finally { con.Close(); }

        }
        // cho phép sinh viên xem danh sách môn mình đã đăng kí
        public DataTable ListAdmissionFalse(int codestudent)
        {
            DataTable tbl;
            con = conn.GetConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("SP_listAdmissionFalse", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CodeProfileStudent", SqlDbType.Int).Value = codestudent;
                da = new SqlDataAdapter(cmd);
                tbl = new DataTable();
                da.Fill(tbl);

            }
            catch (Exception e) { throw; }
            finally { con.Close(); }
            return tbl;
        }
        public DataTable ListAdmissionTrue(int codestudent)
        {
            DataTable tbl;
            con = conn.GetConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("SP_listAdmissionTrue", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CodeProfileStudent", SqlDbType.Int).Value = codestudent;
                da = new SqlDataAdapter(cmd);
                tbl = new DataTable();
                da.Fill(tbl);

            }
            catch (Exception e) { throw; }
            finally { con.Close(); }
            return tbl;
        }
        public string StatusShow(int CodeSchoolSubjects, int CodeProfileStudent)
        {
            string Name = null;
            SqlConnection con = conn.GetConnection();
            try
            {
                con.Open();
                SqlCommand comm = new SqlCommand("SP_ShowStatus", con);
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.Add("@CodeProfileStudent", SqlDbType.Int).Value = CodeProfileStudent;
                comm.Parameters.Add("@CodeSchoolSubjects", SqlDbType.Int).Value = CodeSchoolSubjects;
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    Name = dr.GetString(0);
                    return Name;
                }
                else
                {
                    Name = null;
                    return Name;
                }
            }
            catch (Exception e) { throw; }
            finally { con.Close(); }

        }
    }
}
